﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Chire.ChireInter.Students
{
    public class Student_Action
    {
        #region 获取所有学生
        public List<Student_Model> getAllStudent(string status,string classId)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();


            string strselect = "";
            if (classId.Length > 0)
            {
                strselect = "select * from student where status = '" + status + "' and classid = '" + classId + "'";
            }
            else {
                strselect = "select * from student where status = '" + status + "'";
            }
               
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<Student_Model> music_ablumList = new List<Student_Model>();
            for (int i = 0; i < rows; i++)
            {
                string id = dt.Rows[i]["id"].ToString();
                string name = dt.Rows[i]["name"].ToString();
                string age = dt.Rows[i]["age"].ToString();
                string gender = dt.Rows[i]["gender"].ToString();

                string avatar = dt.Rows[i]["avatar"].ToString();
                string phone = dt.Rows[i]["phone"].ToString();
                string school = dt.Rows[i]["school"].ToString();
                string snumber = dt.Rows[i]["snumber"].ToString();
                string parentnumber = dt.Rows[i]["parentnumber"].ToString();
                string qq = dt.Rows[i]["qq"].ToString();
                string wechat = dt.Rows[i]["wechat"].ToString();
                string jointime = dt.Rows[i]["jointime"].ToString();
                Student_Model downloadModel = new Student_Model()
                {
                    id = id,
                    name = name,
                    age = age,
                    gender = gender,
                    avatar = avatar,
                    phone = phone,
                    school = school,
                    snumber = snumber,
                    parentnumber = parentnumber,
                    qq = qq,
                    wechat = wechat,
                    jointime = jointime
                };
                music_ablumList.Add(downloadModel);
            }
            sqlcon.Close();
            return music_ablumList;
  
        }
        #endregion

        #region 根据学生id删除学生
        public void studentDeleteWithId(string id) {
            //连接数据库
            SqlConnection sqlcon1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon1.Open();
            //修改数据信息
            string strSqls = "update student set status = 0  where id =" + id + "";

            SqlCommand cmd = new SqlCommand(strSqls, sqlcon1);
            //添加参数并且设置参数值
            cmd.ExecuteNonQuery();
            sqlcon1.Close();
        }

        #endregion

        #region 根据学生id获取学生详情
        public Student_Model getStudentInfoWithId(string id) {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select top 1 * from student where id = '" + id + "'";
            SqlCommand sqlcmd = new SqlCommand(strselect, sqlcon);
            SqlDataReader dr = sqlcmd.ExecuteReader();
            Student_Model studentModel = new Student_Model();
            if (dr.Read())
            {
                studentModel.id = id;
                studentModel.name = dr["name"].ToString();
                studentModel.age = dr["age"].ToString();
                studentModel.gender = dr["gender"].ToString();
                studentModel.avatar = dr["avatar"].ToString();
                studentModel.phone = dr["phone"].ToString();
                studentModel.school = dr["school"].ToString();
                studentModel.snumber = dr["snumber"].ToString();
                studentModel.parentnumber = dr["parentnumber"].ToString();
                studentModel.qq = dr["qq"].ToString();
                studentModel.wechat = dr["wechat"].ToString();
                studentModel.jointime = dr["jointime"].ToString();
                studentModel.mark = dr["mark"].ToString();
                studentModel.infomation = dr["infomation"].ToString();
                studentModel.classid = dr["classid"].ToString();
                studentModel.status = dr["status"].ToString();
                studentModel.authStatus = dr["authStatus"].ToString();
                studentModel.openid = dr["openid"].ToString();
            }
            sqlcon.Close();
            return studentModel;
        }
        #endregion

        #region 2.根据班级，获取所有的学生
        public List<Student_Model> getAllStudentWithClassId(string classId)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select * from student where status = '1' and classid = '" + classId + "'";
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<Student_Model> music_ablumList = new List<Student_Model>();
            for (int i = 0; i < rows; i++)
            {
                string id = dt.Rows[i]["id"].ToString();
                string name = dt.Rows[i]["name"].ToString();
                string age = dt.Rows[i]["age"].ToString();
                string gender = dt.Rows[i]["gender"].ToString();

                string avatar = dt.Rows[i]["avatar"].ToString();
                string phone = dt.Rows[i]["phone"].ToString();
                string school = dt.Rows[i]["school"].ToString();
                string snumber = dt.Rows[i]["snumber"].ToString();
                string parentnumber = dt.Rows[i]["parentnumber"].ToString();
                string qq = dt.Rows[i]["qq"].ToString();
                string wechat = dt.Rows[i]["wechat"].ToString();
                string jointime = dt.Rows[i]["jointime"].ToString();

                string mark = dt.Rows[i]["mark"].ToString();
                string infomation = dt.Rows[i]["infomation"].ToString();
                string openid = dt.Rows[i]["openid"].ToString();
                string unionid = dt.Rows[i]["unionid"].ToString();
                string classid = dt.Rows[i]["classid"].ToString();
                string status = dt.Rows[i]["status"].ToString();

                Student_Model downloadModel = new Student_Model()
                {
                    id = id,
                    name = name,
                    age = age,
                    gender = gender,
                    avatar = avatar,
                    phone = phone,
                    school = school,
                    snumber = snumber,
                    parentnumber = parentnumber,
                    qq = qq,
                    wechat = wechat,
                    jointime = jointime,
                    mark = mark,
                    infomation = infomation,
                    openid = openid,
                    unionid = unionid,
                    classid = classid,
                    status = status
                };
                music_ablumList.Add(downloadModel);
            }
            sqlcon.Close();
            return music_ablumList;
        }
        #endregion

        #region 根据微信id 寻找学生id
        public Student_Model getStudentIdWithOpenId(string openid)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select top 1 * from student where openid = '" + openid + "'";
            SqlCommand sqlcmd = new SqlCommand(strselect, sqlcon);
            SqlDataReader dr = sqlcmd.ExecuteReader();
            Student_Model studentModel = new Student_Model();
            if (dr.Read())
            {
                studentModel.id = dr["id"].ToString();
                studentModel.classid = dr["classid"].ToString();
                studentModel.name = dr["name"].ToString();
            }
            sqlcon.Close();
       
            return studentModel;
        }

        #endregion

        #region 根据openid 获取学生信息
        public Student_Model getStudentInfoWithStudentOpenId(string openid)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select top 1 * from student where openid = '" + openid + "'";
            SqlCommand sqlcmd = new SqlCommand(strselect, sqlcon);
            SqlDataReader dr = sqlcmd.ExecuteReader();
            Student_Model studentModel = new Student_Model();
            if (dr.Read())
            {
                studentModel.id = dr["id"].ToString();
                studentModel.age = dr["age"].ToString();
                studentModel.gender = dr["gender"].ToString();
                studentModel.avatar = dr["avatar"].ToString();
                studentModel.phone = dr["phone"].ToString();
                studentModel.school = dr["school"].ToString();
                studentModel.snumber = dr["snumber"].ToString();
                studentModel.parentnumber = dr["parentnumber"].ToString();
                studentModel.qq = dr["qq"].ToString();
                studentModel.wechat = dr["wechat"].ToString();
                studentModel.name = dr["name"].ToString();
                studentModel.jointime = dr["jointime"].ToString();
                studentModel.mark = dr["mark"].ToString();
                studentModel.infomation = dr["infomation"].ToString();
                studentModel.openid = dr["openid"].ToString();
                studentModel.unionid = dr["unionid"].ToString();
                studentModel.classid = dr["classid"].ToString();
                studentModel.status = dr["status"].ToString();
                studentModel.authStatus = dr["authStatus"].ToString();
            }
            sqlcon.Close();

            return studentModel;
        }
        #endregion

        #region 修改学生信息
        public void updateStudentInfoWithopenId(string openId, string key, string value)
        {
            //连接数据库
            SqlConnection sqlcon1 = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon1.Open();
            //修改数据信息
            string strSqls = "update student set " + key + " = '" + value + "'  where openid ='" + openId + "'";


            SqlCommand cmd = new SqlCommand(strSqls, sqlcon1);
            //添加参数并且设置参数值
            cmd.ExecuteNonQuery();
            sqlcon1.Close();
        }

        #endregion

        #region 模糊查询学生 
        public List<Student_Model> studentSearchWithKeyword(string keyword)
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "";
            strselect = "select * from student where gender like '"+keyword+"' or school like '%"+keyword+"%'or name like '%"+keyword+"%'";
         
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<Student_Model> music_ablumList = new List<Student_Model>();
            for (int i = 0; i < rows; i++)
            {
                string id = dt.Rows[i]["id"].ToString();
                string name = dt.Rows[i]["name"].ToString();
                string age = dt.Rows[i]["age"].ToString();
                string gender = dt.Rows[i]["gender"].ToString();

                string avatar = dt.Rows[i]["avatar"].ToString();
                string phone = dt.Rows[i]["phone"].ToString();
                string school = dt.Rows[i]["school"].ToString();
                string snumber = dt.Rows[i]["snumber"].ToString();
                string parentnumber = dt.Rows[i]["parentnumber"].ToString();
                string qq = dt.Rows[i]["qq"].ToString();
                string wechat = dt.Rows[i]["wechat"].ToString();
                string jointime = dt.Rows[i]["jointime"].ToString();
                Student_Model downloadModel = new Student_Model()
                {
                    id = id,
                    name = name,
                    age = age,
                    gender = gender,
                    avatar = avatar,
                    phone = phone,
                    school = school,
                    snumber = snumber,
                    parentnumber = parentnumber,
                    qq = qq,
                    wechat = wechat,
                    jointime = jointime
                };
                music_ablumList.Add(downloadModel);
            }
            sqlcon.Close();
            return music_ablumList;
        }
        #endregion

    }
}